package edu.uoc.tds.pac4.beansGestors;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import edu.uoc.tds.pac4.DAO.GestorDisc;
import edu.uoc.tds.pac4.DAO.Select;
import edu.uoc.tds.pac4.beans.Centre;
import edu.uoc.tds.pac4.beans.EstPercentatgeAsis;
import edu.uoc.tds.pac4.beans.TipusEvent;
import edu.uoc.tds.pac4.beans.Universitat;
import edu.uoc.tds.pac4.common.GestioEventsException;

public class GestorEstPercentatgeAsis extends GestorDisc {
	private static final long serialVersionUID = 1L;

	GestorEstPercentatgeAsis gEP;
	
	protected final static String getColumnes="ie.u_nom , ie.c_nom, ie.e_descripcio, ie.t_nom, ie.e_inici, ie.e_fi, ie.e_capacitat, ie.inscrits, COUNT(*) AS Assistents, (COUNT(*)*100/ie.inscrits) AS Assistencia";
	
	public GestorEstPercentatgeAsis(){
		super();
		//this.gEP = new GestorEstPercentatgeAsis();
	}
	
	public Vector<EstPercentatgeAsis> getInforme(Universitat uni, Centre cen, TipusEvent te, Date dataInici, Date dataFi, int assisMin, int assisMax) throws GestioEventsException{
		Vector<EstPercentatgeAsis> vec = new Vector<EstPercentatgeAsis>();
		// Opcions de filtre
		String sqlWhere = "";
		boolean isFirstWhere = true;
		
		String sqlHaving = "";
		boolean isFirstHaving = true;
		
		if(cen != null)
		{
			if(isFirstWhere) isFirstWhere = false;
			else sqlWhere += " AND";
			sqlWhere += " ie.c_codi_centre = " + this.putString(cen.getCodi());
		}
		else if(uni != null)
		{
			if(isFirstWhere) isFirstWhere = false;
			else sqlWhere += " AND";
			sqlWhere += " ie.u_codi_universitat = " + this.putString(uni.getCodi()) ;
		}
		if(te != null) {
			if(isFirstWhere) isFirstWhere = false;
			else sqlWhere += " AND";
			sqlWhere += " ie.t_codi_tevent = " + this.putString(te.getCodi());
		}
		if(dataInici != null) {
			if(isFirstWhere) isFirstWhere = false;
			else sqlWhere += " AND";
			sqlWhere += " ie.e_inici >= " + this.putDate(dataInici);
		}
		if(dataFi != null) {
			if(isFirstWhere) isFirstWhere = false;
			else sqlWhere += " AND";
			sqlWhere += " ie.e_fi <= " + this.putDate(dataFi) + " ";
		}
		
		
		if(assisMin>0) {
			if(isFirstHaving) isFirstHaving = false;
			else sqlHaving += " AND";
			sqlHaving += " (COUNT(*)*100/ie.inscrits) >= " + this.putNumber(assisMin);
		}
		if(assisMax>0) {
			if(isFirstHaving) isFirstHaving = false;
			else sqlHaving += " AND";
			sqlHaving += " (COUNT(*)*100/ie.inscrits) <= " + this.putNumber(assisMax);
		}
		
		Select sel = new Select("InscritsAEvent ie ", sqlWhere);
		
		sel.setGroupBy(" ie.u_nom , ie.c_nom, ie.e_descripcio, ie.t_nom, ie.e_inici, ie.e_fi, ie.e_capacitat, ie.inscrits ");
		
		sel.setHaving(sqlHaving);
				
		sel.addLeftJoin(" participen pt ", " pt.codi_event = ie.e_codi_event AND pt.assitencia='t' ");

		sel.addCamp(getColumnes);
		sel.setOrderBy("ie.e_inici", "ASC");
		
		ResultSet rs = this.llegir(sel);
		
		if(rs!=null){
			try {
				do {
					EstPercentatgeAsis ePA = new EstPercentatgeAsis();
					ePA.buildFromRS(rs);
					vec.add(ePA);
				} while(rs.next());
				rs.close();
			} 
			catch (SQLException e) 
			{
				throw new GestioEventsException(GestioEventsException.Error_BD);
			}
		}
		return vec;
	}
}

